package cn.gson.crm.model.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;

public class Jdbc {

	private static Jdbc instance;
	private DruidPooledConnection conn;
	private PreparedStatement statement;
	private ResultSet resultSet;
	private  DruidDataSource druid;

	private Jdbc() {
			druid=new DruidDataSource();
			druid.setDriverClassName("com.mysql.jdbc.Driver");
			druid.setUsername("root");
			druid.setPassword("root");
			druid.setUrl("jdbc:mysql://localhost:3306/crm");
			druid.setInitialSize(5);
			druid.setMinIdle(1);
			druid.setMaxActive(10);
			druid.setSharePreparedStatements(false);
	}

	public static Jdbc getInstace() {
		if (instance == null) {
			instance = new Jdbc();
		}
		return instance;
	}
	
	/**
	 * 创建连接
	 */
	private void createConnection() {
		try {
			// 判断数据库连接如果是空或者已经关闭，就重写创建
			if (conn == null || conn.isClosed()) {
				conn =druid.getConnection();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 执行所有的增，删，改sql语句
	 * 
	 * @param sql
	 * @param objects
	 * @return
	 * @throws SQLException
	 */
	public boolean executeUpdate(String sql, Object... objects) throws SQLException {
		boolean result;
		try {
			createStatement(sql, objects);
			// 执行sql
			result = statement.executeUpdate() > 0;
		} finally {
			// 释放资源
			this.release();
		}
		return result;
	}

	/**
	 * 只处理返回一条记录的sql
	 * 
	 * @param sql
	 * @param objects
	 * @throws SQLException
	 */
	public Map<String, Object> executeQuery(String sql, Object... objects) throws SQLException {

		Map<String, Object> row = null;

		try {
			createStatement(sql, objects);
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				row = new HashMap<>();
				ResultSetMetaData meta = resultSet.getMetaData();
				for (int i = 1; i <= meta.getColumnCount(); i++) {
					String lable = meta.getColumnLabel(i);
					row.put(lable, resultSet.getObject(lable));
				}
			}
		} finally {
			//释放资源
			this.release();
		}
	
		return row;
	}

	/**
	 * 返回多条记录
	 * 
	 * @param sql 
	 * @param objects
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> executeQueryForList(String sql, Object... objects) throws SQLException {

		List<Map<String, Object>> rows;
		try {
			createStatement(sql, objects);
			resultSet = statement.executeQuery();
			rows = new ArrayList<>();
			Map<String, Object> row;
			while (resultSet.next()) {
				row = new HashMap<>();
				ResultSetMetaData meta = resultSet.getMetaData();
				for (int i = 1; i <= meta.getColumnCount(); i++) {
					String lable = meta.getColumnName(i);
					row.put(lable, resultSet.getObject(lable));
				}
				rows.add(row);
			}
		} finally {
			this.release();
		}
	
		return rows;
	}
	
	/**
	 * 创建statement对象
	 * @param sql
	 * @param objects
	 * @throws SQLException
	 */
	private void createStatement(String sql, Object... objects) throws SQLException {
			createConnection();
			statement = conn.prepareStatement(sql);
			// 统一的绑定参数
			if (objects != null && objects.length > 0) {
				for (int i = 1; i <= objects.length; i++) {
					statement.setObject(i, objects[i - 1]);
				}
			}
		
	}

	/**
	 * 释放资源
	 */
	private void release() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (conn != null) {
			 conn.recycle();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
